package com.dengtacj.puppeteerspider.actions;


import com.dengtacj.puppeteerspider.utils.JDBCTemplateUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;


/**
 *  清理过期的数据
 */
@Slf4j
public class ClearOldDataAction {

    private JdbcTemplate jdbcTemplate;

    public ClearOldDataAction() {
        this.jdbcTemplate = new JDBCTemplateUtil().jdbcTemplate();
    }

    /**
     * @description 清除30天以前的数据
     */
    public void del_30days_ago(){
        String latestDateSQL = "SELECT * from sp_announcement_crawled_info_letters WHERE  true  ORDER BY  sta_time DESC LIMIT 1";

        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(latestDateSQL);
        String sta_time = "";
        if(sqlRowSet.next()){
             sta_time = sqlRowSet.getString("sta_time");
        }
        String sql = "DELETE FROM  sp_announcement_crawled_info_letters WHERE sta_time<(SELECT DATE_ADD(str_to_date(\""+sta_time+"\",'%Y-%m-%d') , INTERVAL - 1 MONTH) AS monthAgo)";
        log.info("del SQL: "+sql);
        jdbcTemplate.execute(sql);
    }
}
